package com.yangjun.utils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.NumberToTextConverter;

import com.google.common.collect.Lists;

public class POIExcelUtils {
	
	/**
	 * 设置Cell单元的值
	 * 
	 * @param cell
	 * @param value
	 */
	public static void setCellValue(Cell cell, Object value) {
		if (value != null) {
			if (value instanceof String) {
				cell.setCellValue((String) value);
			} else if (value instanceof Number) {
				cell.setCellValue(Double.parseDouble(String.valueOf(value)));
			} else if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			} else if (value instanceof Date) {
				cell.setCellValue((Date) value);
			} else {
				cell.setCellValue(value.toString());
			}
		}
	}

	/**
	 * 读取Excel
	 * 外层List表示所有的数据行，内层List表示每行中的cell单元数据位置
	 * 假设获取一个Excel第三行第二个单元格的数据，例子代码：
	 * FileInputStream excelStream = new FileInputStream(path);
	 * List<List<Object>> list = ExcelUtil.readExcel(excelStream);
	 * System.out.println(list.get(2).get(1));//第三行第二列,索引行位置是2,列的索引位置是1
	 * @param excelStream Excel文件流
	 * @param sheetIndex Excel-Sheet 的索引
	 * @return List<List<Object>> 
	 * @throws Exception
	 */
	public static List<List<Object>> readExcel(InputStream excelStream,int sheetIndex){
		List<List<Object>> datas = Lists.newArrayList();
		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(excelStream);
			Sheet sheet = workbook.getSheetAt(sheetIndex);
			int rows = sheet.getPhysicalNumberOfRows();
			for(int i=0;i<rows;i++){
				Row row = sheet.getRow(i);
				short cellNum = row.getLastCellNum();
				List<Object> item = new ArrayList<Object>(cellNum);
				for(int j=0;j<cellNum;j++){
					Cell cell = row.getCell(j);
					Object value = POIExcelUtils.getCellValue(cell);
					if(value==null){
						item.add(null);
//						System.out.print("空"+" ");
					}else{
						item.add(value.toString());
//						System.out.print(value.toString()+" ");
					}
				}
//				System.out.println("==========="+i+"item size:"+item.size());;
				datas.add(item);
			}
//			System.out.println(rows+"=============");
		} catch (EncryptedDocumentException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		}
		return datas;
	}
	
	public static Boolean writeExcel(InputStream inputStream,String path){
		FileOutputStream outputStream = null;
		try {
			outputStream = new FileOutputStream(new File(path));
			byte[] temp = new byte[1024];
			int i = inputStream.read(temp);
			while(i!=-1){
				outputStream.write(temp,0,temp.length);
				outputStream.flush();
				i = inputStream.read(temp);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}finally{
			if(inputStream!=null){
				try {
					inputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(outputStream!=null){
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}
	/**
	 * 获取cell值
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		
		if (null != cell) {
			switch (cell.getCellType()) {
			// 空白
			case BLANK:
				break;
			// Boolean
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			// 错误格式
			case ERROR:
				break;
			// 公式
			case FORMULA:
				Workbook wb = cell.getSheet().getWorkbook();
				CreationHelper crateHelper = wb.getCreationHelper();
				FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
				value = getCellValue(evaluator.evaluateInCell(cell));
				break;
			// 数值
			case NUMERIC:
				// 处理日期格式
				if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = NumberToTextConverter.toText(cell.getNumericCellValue());
				}
				break;
			case STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			default:
				value = null;
			}
		}
		return value;
	}
	
}
